import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
from datetime import datetime

df = pd.read_excel('superstore_sales.xlsx')
df.head() 

Get the number of rows and columns

df.shape

(51290, 21)

Count the number of missing values

df.isnull().sum()

order_id          0
order_date        0
ship_date         0
ship_mode         0
customer_name     0
segment           0
state             0
country           0
market            0
region            0
product_id        0
category          0
sub_category      0
product_name      0
sales             0
quantity          0
discount          0
profit            0
shipping_cost     0
order_priority    0
year              0
dtype: int64

Which is/are the most selling products?

boughts = df.groupby('product_name').agg({'quantity': 'sum'}).sort_values('quantity',ascending=False)[:10]
boughts

sns.barplot(x='product_name',y='quantity',data=boughts.reset_index())
plt.xticks(rotation = 'vertical')

Out [6]:

(array([0, 1, 2, 3, 4, 5, 6, 7, 8, 9]),
 <a list of 10 Text major ticklabel objects>)

Satışı en iyi 10 ürün hangileridir? - Which are the Top 10 products by sales?

productSales = pd.DataFrame(df.groupby('product_name').sum()['sales'])
productSales.sort_values(by=['sales'], inplace=True, ascending=False)
productSales.head(10)

Hangi ürün daha çok kar getirmiş ? Which are the most profitable products?

profitable_products = pd.DataFrame(df.groupby(['product_name']).sum()['profit'])
profitable_products.sort_values(by=['profit'], inplace = True, ascending = False)
profitable_products.head(10)

sns.barplot(x='product_name',y='profit',data=profitable_products.head(10).reset_index())
plt.xticks(rotation = 'vertical')

Out [9]:

(array([0, 1, 2, 3, 4, 5, 6, 7, 8, 9]),
 <a list of 10 Text major ticklabel objects>)

What category sold the most?

df.groupby(['category','sales']).sum()

Which are the most profitable category?

profitable_category = pd.DataFrame(df.groupby(['category']).sum()['profit'])
profitable_category.sort_values(by=['profit'], inplace = True, ascending = False)
profitable_category

plt.figure(figsize=(17, 5))
plt.bar(df['category'],df['sales'],color = 'b')
plt.xticks(size=8)
plt.show()

Total sales values by category and subcategory

df.groupby(['category','sub_category']).sum()

Which are the most selling products in subcategory?

df.groupby(["category", "sub_category"], as_index=False)["quantity"].count()

Which customer segments are the most profitable ?

profitable_segment = pd.DataFrame(df.groupby(['segment']).sum()['profit'])
profitable_segment.sort_values(by=['profit'], inplace = True, ascending = False)
profitable_segment

sns.barplot(x='segment',y='profit',data=profitable_segment.reset_index())
plt.xticks(rotation = 'vertical')

Out [16]:

(array([0, 1, 2]), <a list of 3 Text major ticklabel objects>)

What shipping modes sold the most products?

plt.figure(figsize=(14, 6))
plt.bar(df['ship_mode'],df['sales'],color = 'b')
plt.xticks(rotation='vertical', size=8)
plt.show()

Visualize the 'Category' column from the Shipmode column dataset standpoints.

category_hist = sns.FacetGrid(df, col='ship_mode', palette='rainbow')
category_hist.map(plt.hist, 'category')

Out [18]:

<seaborn.axisgrid.FacetGrid at 0x7fb0a2a31640>

What market sold the most products?

sns.set_style('whitegrid')
sns.countplot(x='market',data=df, palette='rainbow')

Out [19]:

<matplotlib.axes._subplots.AxesSubplot at 0x7fb0bc618640>

Which are the Top 10 country by sales?

countries = pd.DataFrame(df.groupby('country').sum()['sales'])
countries.sort_values('sales',inplace=True, ascending=False)
countries.head(10)

Create a pie chart with the 10 countries that have the most sales

countries = countries.sort_values(by = 'sales',ascending = False)[1:11]
countries['sales'].plot(kind='pie',autopct='%1.1f%%',figsize=(14,7))
plt.title('10 Countries with most sales')
plt.show()

Which are the average shipping cost for top 10 different countries?

df.groupby('country').agg({'shipping_cost':'mean'}).sort_values('shipping_cost', ascending=False).head(10)

Who are the top-10 most profitable customers?

data10 = df.sort_values('profit',ascending=False).head(10)[['order_id','customer_name','profit']]
data10

Adding a new column (month)

month = df['order_date'].astype(str) 
month = month.apply(lambda x:datetime.strptime(x,'%Y-%m-%d'))
df['month'] = month.map(lambda x: x.month) 
df

Total sales values by year and month

date = df.groupby(['year','month']).sum()
date

Total sales chart by the year

sns.barplot(x='year',y='sales',data=date.reset_index())
plt.xticks(rotation = 'vertical')

Out [27]:

(array([0, 1, 2, 3]), <a list of 4 Text major ticklabel objects>)